package com.lzhl.buildjar.dao.impl;

import com.lzhl.buildjar.dao.BaseDao;
import com.lzhl.buildjar.dao.CategoryDao;
import com.lzhl.buildjar.exception.DaoException;
import com.lzhl.buildjar.po.Category;
import com.lzhl.buildjar.po.Scheme;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * @author 李柱良(@柱子的日月)
 * @date 2014/6/20
 */
public class CategoryDaoImpl extends BaseDao implements CategoryDao {

    @Override
    public Category getCategory(long id) {
        if(id == Category.ROOT.getId()) {
            return Category.ROOT;
        }

        Connection conn;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("select * from T_Category \n");
            sql.append("where id = ? \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, id);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                Category category = new Category(id, rs.getString("FName"));
                category.setParent(new Category(rs.getLong("FParentID")));
                category.setSeq(rs.getInt("FSeq"));
                return category;
            }
        }
        catch (Exception e) {
            throw new DaoException("分类查询失败", e);
        }
        finally {
            close(pstmt, rs);
        }

        return null;
    }

    @Override
    public List<Category> getCategories(Category parent) {
        List<Category> categories = new ArrayList<Category>();

        Connection conn;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("select * from T_Category \n");
            sql.append("where FParentID = ? \n");
            sql.append("order by FName \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, parent.getId());

            rs = pstmt.executeQuery();
            while (rs.next()) {
                Category category = new Category(rs.getLong("id"), rs.getString("FName"));
                category.setParent(parent);
                category.setSeq(rs.getInt("FSeq"));
                categories.add(category);
            }
        }
        catch (Exception e) {
            throw new DaoException("分类查询失败", e);
        }
        finally {
            close(pstmt, rs);
        }

        return categories;
    }

    @Override
    public Category getCategoryTree(long sonId) {
        Category son = getCategory(sonId);
        if(son == null) {
            throw new DaoException("分类[" + sonId + "]不存在！");
        }

        Set<Long> ids = new HashSet<Long>();
        ids.add(son.getId());
        Category me = son;
        while(true) {
            if(me.equals(Category.ROOT)) {
                break;
            }

            Category parent = getCategory(me.getParent().getId());
            if(parent == null) {
                throw new DaoException("分类[" + me.getParent().getId() + "]不存在！");
            }
            if(ids.contains(parent.getId())) {
                throw new DaoException("分类[" + sonId + "]存在循环！");
            }
            else {
                ids.add(parent.getId());
            }
            me.setParent(parent);
            me = parent;
        }

        return son;
    }

    @Override
    public Category addCategory(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("insert into T_Category \n");
            sql.append("(FParentID, FSeq, FName) \n");
            sql.append("values \n");
            sql.append("(?, ?, ?) \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, category.getParent().getId());
            pstmt.setInt(2, category.getSeq());
            pstmt.setString(3, category.getName());

            pstmt.executeUpdate();
            pstmt.close();

            sql.setLength(0);
            sql.append("SELECT LAST_INSERT_ROWID()");

            pstmt = conn.prepareStatement(sql.toString());
            rs = pstmt.executeQuery();

            if(rs.next()) {
                category.setId(rs.getLong(1));
            }

            conn.commit();
        }
        catch (Exception e) {
            rollback(conn);
            throw new DaoException("分类添加失败", e);
        }
        finally {
            close(pstmt, rs);
        }

        return category;
    }

    @Override
    public Category saveCategory(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("update T_Category set \n");
            sql.append("FParentID = ?, FSeq = ?, FName = ? \n");
            sql.append("where id = ? \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, category.getParent().getId());
            pstmt.setInt(2, category.getSeq());
            pstmt.setString(3, category.getName());
            pstmt.setLong(4, category.getId());

            pstmt.executeUpdate();

            conn.commit();
        }
        catch (Exception e) {
            rollback(conn);
            throw new DaoException("分类保存失败", e);
        }
        finally {
            close(pstmt, rs);
        }

        return category;
    }

    @Override
    public void deleteCategory(long id) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("delete from T_Category \n");
            sql.append("where id = ? \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, id);

            pstmt.executeUpdate();

            conn.commit();
        }
        catch (Exception e) {
            rollback(conn);
            throw new DaoException("分类删除失败", e);
        }
        finally {
            close(pstmt);
        }
    }

    public boolean isExists(Category parent, String name) {
        Connection conn;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer();
            sql.append("select 1 from T_Category \n");
            sql.append("where FParentID = ? and FName = ? \n");

            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1, parent.getId());
            pstmt.setString(2, name);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            }
        }
        catch (Exception e) {
            throw new DaoException("分类查询失败", e);
        }
        finally {
            close(pstmt, rs);
        }

        return false;
    }
}
